/*
 * 中北大学软件学院
 */
package com.ychs.uolab.user.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ychs.uolab.user.vo.User;
import com.ychs.uolab.util.DbUtil;
import com.ychs.uolab.util.Md5Util;

/**
 * UserDao实现类
 * 
 * @author djl
 * @version 1.0
 */
public class UserDaoImpl implements UserDao {

	@Override
	public boolean login(String userName, String userPsw) {
		boolean loginFlag = false;
		// 数据库连接
		Connection connection = null;
		// 结果集，查询得到的结果
		ResultSet rSet = null;
		// 准备并执行sql语句
		PreparedStatement pStatement = null;

		// 打开数据库连接
		connection = DbUtil.getConnection();
		// 准备SELECT语句, ?是占位符
		String sql = "select * from t_user where username = ? and password = ?";
		try {
			pStatement = connection.prepareStatement(sql);
			// 给?赋值
			pStatement.setString(1, userName);
			pStatement.setString(2, userPsw);
			// 执行sql语句，下达执行命令
			rSet = pStatement.executeQuery();

			if (rSet.next()) {
				loginFlag = true;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtil.closeResource(rSet, pStatement, connection);
		}
		return loginFlag;
	}

	@Override
	public boolean insertUser(User user) {
		boolean flag = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		conn = DbUtil.getConnection();
		String sql = "insert into t_user values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, user.getUid());
			pstmt.setString(2, user.getUsername());
			pstmt.setString(3, Md5Util.getMD5(user.getPassword()));
			pstmt.setString(4, user.getRealname());
			pstmt.setString(5, user.getSex());
			pstmt.setString(6, user.getTelephone());
			pstmt.setString(7, user.getDname());
			pstmt.setString(8, user.getMajor());
			pstmt.setString(9, user.getCname());
			pstmt.setString(10, user.getGrade());
			pstmt.setString(11, user.getDuty());
			pstmt.setString(12, user.getQq());
			pstmt.setString(13, user.getStatus());
			pstmt.setString(14, user.getRemark());
			//这个方法执行的是insert，update，delete语句
			//如果执行成功返回非0int，失败返回0
			int count = pstmt.executeUpdate();
			if(count != 0) {
				//添加成功，flag为true
				flag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbUtil.closeResource(null, pstmt, conn);
		}
		return flag;
	}

	@Override
	public int updateUser(User user) {
		Connection conn = DbUtil.getConnection();
		PreparedStatement pstmt =null;
		int count = 0;
		
		String sql = "update t_user set telephone=?,qq=?,duty=?,remark=? where uid=?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, user.getTelephone());
			pstmt.setString(2, user.getQq());
			pstmt.setString(3, user.getDuty());
			pstmt.setString(4, user.getRemark());
			pstmt.setInt(5, user.getUid());
			//count表示的是执行的SQL影响的记录行数
			count = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbUtil.closeResource(null, pstmt, conn);
		}
		return count;
	}

	@Override
	public int deleteUser(String userid) {
		Connection conn = DbUtil.getConnection();
		PreparedStatement pstmt =null;
		int count = 0;
		
		String sql = "DELETE FROM t_user WHERE uid=?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, Integer.parseInt(userid));
			//count表示的是执行的SQL影响的记录行数
			count = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbUtil.closeResource(null, pstmt, conn);
		}
		return count;
	}

	@Override
	public List<User> selsctUser(String username, String dname, String major, String classes, String grade) {
		Connection conn = DbUtil.getConnection();
		Statement stmt = null;
 		ResultSet rs = null;
		List<User> userList =new ArrayList<User>();
		//因为我们不知道用户具体填了几个，所以SQL语句是动态变化的
		String sql = "SELECT * FROM t_user WHERE 1=1";
		//可变字符串
		StringBuilder strBuilder = new StringBuilder(sql);
		if(username != null) {
			strBuilder.append(" AND username='" + username + "'");
		}
		if(dname != null) {
			strBuilder.append(" AND dname='" + dname + "'");
		}
		if(major != null) {
			strBuilder.append(" AND major_name='" + major + "'");
		}
		if(classes != null) {
			strBuilder.append(" AND cname='" + classes + "'");
		}
		if(grade != null) {
			strBuilder.append(" AND grade='" + grade + "'");
		}
		sql  = strBuilder.toString();
		System.out.println(sql);
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);//执行查询
			//把rs结果集里的数据转移到集合userList中
			while(rs.next()) {
				User user = new User();
				//rs.get里的username是表里的字段
				user.setUsername(rs.getString("username"));
				user.setDname(rs.getString("dname"));
				user.setMajor(rs.getString("major_name"));
				user.setCname(rs.getString("cname"));
				user.setGrade(rs.getString("grade"));
				user.setTelephone(rs.getString("telephone"));
				user.setQq(rs.getString("qq"));
				user.setDuty(rs.getString("duty"));
				user.setStatus(rs.getString("status"));
				userList.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbUtil.closeResource(rs, null, conn);
		}
		
		return userList;
	}
}
